import pymysql


class DBUtil:
    # 定义变量
    conn = None

    # 创建连接conn
    @classmethod
    def __connect_db(cls):
        cls.conn = pymysql.connect(host="121.43.169.97", port=3306, user="student", password="P2P_student_2022",
                                   database="czbk_member", charset="utf8")
        return cls.conn

    # 关闭连接conn
    @classmethod
    def __close_db(cls):
        cls.conn.close()

    # 定义查询数据库的方法
    @classmethod
    def get_one(cls, sql):

        # 定义变量
        cursor = None
        res = None

        try:
            # 创建连接conn
            cls.conn = cls.__connect_db()
            # 获取游标
            cursor = cls.conn.cursor()
            # 执行sql,获取结果，把结果返回
            cursor.execute(sql)
            res = cursor.fetchone()
        except Exception as err:
            print("查询数据库错误日志=", str(err))
        finally:
            # 关闭游标
            cursor.close()
            # 关闭连接conn
            cls.__close_db()
            return res

    # 定义增删改数据库的方法
    @classmethod
    def uid_db(cls, sql):
        cursor = None
        try:
            # 创建连接conn
            cls.conn = cls.__connect_db()
            # 获取游标
            cursor = cls.conn.cursor()
            # 执行sql,打印影响了数据库多少行
            cursor.execute(sql)
            affect_rows = cls.conn.affected_rows()
            print("影响数据库的行数=", affect_rows)
            #  事务的提交、回顾
            cls.conn.commit()
        except Exception as err:
            print("增删改错误日志=",str(err))
            cls.conn.rollback()
        finally:
            # 关闭游标
            cursor.close()
            # 关闭连接conn
            cls.__close_db()


    @classmethod
    def clear_data(cls):
        sql1 = """
            delete i.* from mb_member_info i INNER JOIN mb_member m on i.member_id=m.id where m.phone in ("13600001111","13600001112","13600001113","13600001114")
            """
        sql2 = """
            delete l.* from mb_member_login_log l INNER JOIN mb_member m on l.member_id=m.id where m.phone in ("13600001111","13600001112","13600001113","13600001114")
            """
        sql3 = """
            delete from mb_member_register_log where phone in ("13600001111","13600001112","13600001113","13600001114")
            """
        sql4 = """
            delete from mb_member where phone in ("13600001111","13600001112","13600001113","13600001114")
            """
        DBUtil.uid_db(sql1)
        DBUtil.uid_db(sql2)
        DBUtil.uid_db(sql3)
        DBUtil.uid_db(sql4)


if __name__ == '__main__':
    # result_query = DBUtil.get_one("SELECT  * from t_book ;")
    # print("result_query=",result_query)
    update_sql = "UPDATE t_book set title = '三国志' WHERE  id = 6;"
    result_update = DBUtil.uid_db(update_sql)
